package si.assign2.ejb;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

import javax.persistence.*;

import si.assign2.model.*;


/**
 * 
 * @author quanben
 * at the moment it's not actually able to be said as an EJB
 * but it works as a bean to serve the page request at least 
 */
@PersistenceContext(name = "myEntityManager", unitName = "bean-unit")
public class ProductBean implements ProductOperations {

	static EntityManagerFactory emf; 
	static String golsUserName;
	static String golsPassword;

	@PersistenceContext(unitName = "member-unit")
	public EntityManager em;
	
	protected Object getObjectByName(String name, String queryName) {
		Query q = em.createNamedQuery(queryName);
		q.setParameter("name", name);
		try {
			Object object = q.getSingleResult();
			return object;
		} catch (Exception e) {
			return null;
		}
	}
	
	protected Category getCategoryByName(String name) {
		return (Category)getObjectByName(name, "getCategoriesByName");
	}
	
	protected ProductType getProductTypeByName(String name) {
		return (ProductType)getObjectByName(name, "getProductTypesByName");
	}
	
	protected Product getProductByName(String name) {
		return (Product)getObjectByName(name, "getProductByName");
	}
		
	protected ProductTypeAttributeValue getProductTypeAttributeValue(
			String productName, String attributeName, boolean createIfNonExist) {
		//1. check the existence of the product, if not return false
		Product p = getProductByName(productName);
		if (p == null) {	// product doesn't exist
			return null;
		}
		
		//2. get the product type the product belongs to

		ProductType pt = p.getProductType(); 
		
		//3. look for the attribute object with specific name 
		//   and associated with the product type obtained
		/*
		 * get the attribute associated to the type
		 * rather than simply get it by name to avoid getting
		 * a duplicate one 
		 */
		Set<ProductTypeAttribute> ptas = pt.getProductTypeAttributes();
		ProductTypeAttribute pta = null;
		for (ProductTypeAttribute tPta : ptas) {
			if (tPta.getName().equals(attributeName)) {
				pta = tPta;
				break;
			}
		}
				
		//4. if such an attribute doesn't exist, create one like that
		if (pta == null)
		{
			if (!createIfNonExist)
				return null;
			
			pta = new ProductTypeAttribute();
			pta.setName(attributeName);
			pta.setIsRequired('N');	// always set to N for simplicity	
			pt.addProductTypeAttribute(pta);
			em.persist(pta);
		}
	
		//5. look for the attribute value object associated with both 
		//   the product and the attribute
		Set<ProductTypeAttributeValue> ptavs = pta.getProductTypeAttributeValues();
		ProductTypeAttributeValue ptav = null;
		for (ProductTypeAttributeValue tPtav : ptavs) {
			if (tPtav.getProduct().getId() == p.getId()) {
				ptav = tPtav;
				break;
			}
		}

		//6. if such an attribute value doesn't exist, create one like that
		if (ptav == null)
		{
			if (!createIfNonExist)
				return null;
			
			ptav = new ProductTypeAttributeValue();
			
			pta.addProductTypeAttributeValue(ptav);
			p.addProductTypeAttributeValue(ptav);
			em.persist(ptav);	
		}
		
		return ptav;
	}	
	
	public static void createEntityManagerFactory() {
		emf = Persistence.createEntityManagerFactory("assign2PU");
	}
	
	public static void closeEntityManagerFactory() {
		emf.close();
	}

	public static void setUserAccount(String userName, String password) {
		golsUserName = userName;
		golsPassword = password;
	}
	
	public ProductBean() {
		em = emf.createEntityManager();
	}
	
	public void close() {
		em.close();
	}
	
	
	public boolean createProduct(String productName, String sku, 
			String description, double purchasePrice, double salePrice,
			String productTypeName) {
		// TODO Auto-generated method stub
		
		Product p = getProductByName(productName);
		
		if (p != null) {	// product already exists
			return false;
		}
		
		p = new Product();
		p.setName(productName);
		p.setSku(sku);
		p.setDescription(description);
		p.setPurchasePrice(purchasePrice);
		p.setSalePrice(salePrice);
		em.persist(p);
		
		ProductType pt = getProductTypeByName(productTypeName);
		if (pt != null) {	// product type already exists
			pt.addProduct(p);
		} else {	// product type does not yet exist
			pt = new ProductType();
			pt.setName(productTypeName);
			pt.addProduct(p);
			em.persist(pt);
		}

		return true;
	}

	@Override
	public boolean setupCategory(String categoryName, String productTypeName, 
			String parentCategoryName) {
		// TODO Auto-generated method stub
		if (parentCategoryName == null || parentCategoryName.isEmpty()) {
			parentCategoryName = "Root";
		} 
		Category parent = getCategoryByName(parentCategoryName);
	
		Category c = getCategoryByName(categoryName);
		boolean persistC = false;
		boolean persistPt = false;
		boolean persistParent = false;
		
		if (c == null) {
			c = new Category();
			c.setName(categoryName);
			persistC = true;
		}
		
		ProductType pt = getProductTypeByName(productTypeName);
		if (pt == null) {
			pt = new ProductType();
			pt.setName(productTypeName);
			persistPt = true;
		}
		pt.addCategory(c);
		
				
		if (parent == null) {
			return false;
		}
			
		parent.addChild(c);
		
		if (persistC) em.persist(c);
		if (persistPt) em.persist(pt);
		if (persistParent) em.persist(parent);
						
		return true;
	}

	public boolean setProductCategory(String productName, String categoryName) {
		// TODO Auto-generated method stub
		
		Product p = getProductByName(productName);
		if (p == null) {	// product doesn't exist
			return false;
		}
		
		Category c = getCategoryByName(categoryName);
		
		if (c == null) {
			// category doesn't exist, create a new one
			c = new Category();
			c.setName(categoryName);
			Category root = getCategoryByName("Root");
			root.addChild(c);
			em.persist(c);
		}
		
		c.addProduct(p);
				
		return false;
	}
	
	public boolean setProductAttributeValue(String productName,
			String attributeName, String attributeValue) {
		// TODO Auto-generated method stub
		ProductTypeAttributeValue ptav = 
			getProductTypeAttributeValue(productName, attributeName, true);
		
		if (ptav == null)
			return false;
		
		//7. set the value of the attribute value object to the value specified as the last paremeter
		ptav.setValue(attributeValue);
	
		//8. done
		return true;
	}
	
	public String getProductAttributeValue(String productName,
			String attributeName) {
		ProductTypeAttributeValue ptav = 
			getProductTypeAttributeValue(productName, attributeName, false);
		
		if (ptav == null)
			return null;
		
		return ptav.getValue();
	}
	
	public List<String> getProductsOfProductType(String productTypeName) {
		Query q = em.createNamedQuery("listProducts");
		List list = q.getResultList();
		List<String> resultList = new LinkedList<String>();
		for (Object obj : list) {
			Product p = (Product)obj;
			ProductType pt = p.getProductType();
			if (pt.getName().equals(productTypeName)) {
				resultList.add(p.getName());
			}
		}
		return resultList;
	}
	
	@Override
	public List<String> getChildCategories(String categoryName) {
		// TODO Auto-generated method stub
		Query q = em.createNamedQuery("getCategoriesByName");
		q.setParameter("name", categoryName);
		List list = q.getResultList();
		List<String> result = new LinkedList<String>();
		if (list.size() == 0) {
			return result;
		}
		Category category = (Category)list.get(0);
		Set<Category> children = category.getChildren();
		for (Category child : children) {
			result.add(child.getName());
		}
		return result;
	}
		
	public static void cleanup() {
		Connection conn = null; 
		try {
			conn = DriverManager.getConnection("jdbc:db2://204.90.115.30:5025/DALLAS9:retrieveMessagesFromServerOnGetMessage=true;emulateParameterMetaDataForZCalls=1;", 
					golsUserName, golsPassword);
			conn.setAutoCommit(false);
			Statement stmt = conn.createStatement();
			
			int rows1 = stmt.executeUpdate("DELETE FROM PRODUCTS");
			int rows2 = stmt.executeUpdate("DELETE FROM PRODUCT_TYPES WHERE ID<>1");	/* not to delete the root product type */
			int rows3 = stmt.executeUpdate("DELETE FROM PRODUCT_TYPE_ATTRIBUTES");
			int rows4 = stmt.executeUpdate("DELETE FROM PRODUCT_TYPE_ATTRIBUTE_VALUES");
			int rows5 = stmt.executeUpdate("DELETE FROM CATEGORIES WHERE name <> 'Root'");
			int rows6 = stmt.executeUpdate("DELETE FROM PRODUCT_CATEGORIES");
			
			conn.commit();
			System.out.printf("Successfully deleted %d rows for PRODUCTS table\n", rows1);
			System.out.printf("Successfully deleted %d rows for PRODUCT_TYPES table\n", rows2);
			System.out.printf("Successfully deleted %d rows for PRODUCT_TYPE_ATTRIBUTES table\n", rows3);
			System.out.printf("Successfully deleted %d rows for PRODUCT_TYPE_ATTRIBUTE_VALUES table\n", rows4);
			System.out.printf("Successfully deleted %d rows for CATEGORIES table\n", rows5);
			System.out.printf("Successfully deleted %d rows for PRODUCT_CATEGORIES table\n", rows6);
			
			//System.exit(1);
		} catch (SQLException e) {
			if (conn != null) {
				try {
					conn.rollback();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
			}
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}				
			}
		}
	}
	
	public void beginTransaction() {
		em.getTransaction().begin();
	}
	
	public void commitTransaction() {
		em.getTransaction().commit();
	}
	
	public void rollbackTransaction() {
		em.getTransaction().rollback();
	}
	
	public void flush() {
		em.flush();
	}
	
	public String test() {
		// TODO Auto-generated method stub
		return "a test string (ver 1.0)";
	}

	
}
